package com.credit.dao.major;/**
 * @Classname MajorDAO
 * @Description TODO
 * @Date 2020/11/5 17:48
 * @Created by SHTL
 */

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import javax.transaction.Transactional;
import java.util.List;
import java.util.Map;

/**
 * @InterfaceName MajorDAO
 * @Description 专业信息DAO接口
 */
public interface MajorDAO extends JpaRepository<MajorModel,Integer>, JpaSpecificationExecutor<MajorModel> {


    // 根据 majorid 删除
    @Transactional
    @Modifying
    @Query(value = "delete from major_table where majorid=:majorid",nativeQuery = true)
    Integer deleteByMajorid(@Param("majorid") Integer majorid);


    // 根据 collegename 获取 专业信息
    @Query(value = "select m.majorid,m.majorname from major_table as m , college_table as c where c.collegeid=m.collegeid and c.collegename=:collegename",nativeQuery = true)
    List<Map<String,Object>> getMajorByCollegename(@Param("collegename") String collegename);


    // 根据 collegeid 获取 major 信息
    @Query(value = "select * from major_table as a where a.collegeid=:collegeid",nativeQuery = true)
    List<MajorModel> findByCollegeid(@Param("collegeid") Integer collegeid);


    // 根据 majorid 获取 majorname
    MajorModel findByMajorid(Integer majorid);


    // 根据 majorname 获取 majorid
    MajorModel findByMajorname(String majorname);


    // 根据 majorid 获取 当前的专业信息，涉及到其它表的数据
    @Query(value = "select m.majorid,m.majorname,m.content,m.ctime,c.collegename from major_table as m,college_table as c where m.collegeid=c.collegeid and m.majorid=:majorid",nativeQuery = true)
    Map<String,Object> findByMJinMid(@Param("majorid") Integer majorid);


    // 查询 major_table 中 college 的数量，依次来进行条件筛选
    @Modifying
    @Query(value = "select c.collegename from college_table as c,major_table as m where c.collegeid = m.collegeid GROUP BY c.collegename",nativeQuery = true)
    List<Object> getCollegeInMajor();


    /**
     * 获取所有专业信息，条件查询  用于管理员页面，动态查询数据
     * 查询条件： like   college
     * **/
    // 1. 没有查询条件
    @Modifying
    @Query(value = "select m.majorid,m.majorname,m.content,m.ctime,c.collegename from major_table as m,college_table as c where m.collegeid=c.collegeid",nativeQuery = true)
    List<Map<String,Object>> findMJNO();

    // 2. like
    @Modifying
    @Query(value = "select m.majorid,m.majorname,m.content,m.ctime,c.collegename from major_table as m,college_table as c where m.collegeid=c.collegeid and (c.collegename like concat('%',:like,'%') or m.majorname like concat('%',:like,'%'))",nativeQuery = true)
    List<Map<String,Object>> findMJLike(@Param("like") String like);

    // 3. college
    @Modifying
    @Query(value = "select m.majorid,m.majorname,m.content,m.ctime,c.collegename from major_table as m,college_table as c where m.collegeid=c.collegeid and c.collegename=:college",nativeQuery = true)
    List<Map<String,Object>> findMJCollege(@Param("college") String college);

    // 4. like 和 college
    @Modifying
    @Query(value = "select m.majorid,m.majorname,m.content,m.ctime,c.collegename from major_table as m,college_table as c where m.collegeid=c.collegeid and (c.collegename like concat('%',:like,'%') or m.majorname like concat('%',:like,'%')) and c.collegename=:college",nativeQuery = true)
    List<Map<String,Object>> findMJLikeAndCollege(@Param("like") String like,@Param("college") String college);

}
